﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[esf_files].[P_UpdateFileReferenceParents]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [esf_files].P_UpdateFileReferenceParents
GO


CREATE PROCEDURE esf_files.P_UpdateFileReferenceParents
AS
	
	UPDATE esf_files.FileReference SET ParentFileReferenceID = NULL
	UPDATE esf_files.FileReference SET ParentFileRelativeOrder = 0


	UPDATE
		soh
	SET	
		soh.ParentFileReferenceID = X.ParentID
	FROM
		esf_files.FileReference soh
	INNER JOIN
		(SELECT T.*, FRP.ID ParentID FROM
			(SELECT 
				ID
				,URL
				,SUBSTRING (URL, 1,	
					LEN (URL) - CHARINDEX ('_', REVERSE (URL))) +
				 SUBSTRING (URL, LEN (URL) - CHARINDEX ('.', REVERSE (URL)) + 1, LEN (URL)) ParentURL
			FROM 
				esf_files.FileReference 
			) T
			INNER JOIN
				esf_files.FileReference FRP ON 
					FRP.URL = T.ParentURL
			WHERE T.URL <> T.ParentURL) X 
	ON 
		X.ID = soh.ID